﻿using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;

namespace Ryland.DbUtility
{
    /// <summary>
    /// 通用数据库访问基类
    /// </summary>
    public class DbAccessAbstract
    {
        /// <summary>
        /// 静态的 DataTable ExtendedProperties 属性
        /// </summary>
        internal static string CPropSql = "SourceSQL";
        /// <summary>
        /// 连接对象
        /// </summary>
        protected IDbConnection dbConnection;
        /// <summary>
        /// 事务对象
        /// </summary>
        protected IDbTransaction DbTransaction;

        /// <summary>
        /// 构造函数
        /// </summary>
        public DbAccessAbstract()
        {
            // DevNetLicenseProvider.ValidateLicense(typeof (DBConnect), this);
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbConnection">DbConnection</param>
        public DbAccessAbstract(IDbConnection dbConnection)
            : this()
        {
            this.dbConnection = dbConnection;
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~DbAccessAbstract()
        {
            dbConnection = null;
            DbTransaction = null;
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        public virtual void Open()
        {
            if (dbConnection != null)
            {
                if (dbConnection.State == ConnectionState.Broken)
                    dbConnection.Close();
                if (dbConnection.State == ConnectionState.Closed)
                    dbConnection.Open();
            }
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public virtual void Close()
        {
            if (dbConnection != null && dbConnection.State != ConnectionState.Closed)
                dbConnection.Close();
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        public virtual void BeginTransaction()
        {
            if (dbConnection == null || dbConnection.State == ConnectionState.Closed)
                throw new Exception("Connection is Null or Not Open");

            DbTransaction = dbConnection.BeginTransaction();
        }

        /// <summary>
        /// 开始事务
        /// </summary>
        /// <param name="isoLationLevel">事务锁定行为</param>
        public virtual void BeginTransaction(IsolationLevel isoLationLevel)
        {
            if (dbConnection == null || dbConnection.State == ConnectionState.Closed)
                throw new Exception("Connection is Null or Not Open");
            DbTransaction = dbConnection.BeginTransaction(isoLationLevel);
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public virtual void RollBackTransaction()
        {
            if (DbTransaction == null)
                throw new Exception("Transaction Not Begin");

            DbTransaction.Rollback();
            DbTransaction.Dispose();
            DbTransaction = null;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public virtual void CommitTransaction()
        {
            if (DbTransaction == null)
                throw new Exception("Transaction Not Begin");

            DbTransaction.Commit();
            DbTransaction.Dispose();
            DbTransaction = null;
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="direction"></param>
        /// <param name="paraType"></param>
        /// <param name="size"></param>
        /// <returns></returns>
        public DbParameter CreateCmdParameter(DbCommand cmd, string parameterName, ParameterDirection direction, DbType paraType, int size)
        {
            DbParameter parameter = cmd.CreateParameter();
            parameter.ParameterName = parameterName;
            parameter.Direction = direction;
            if (dbConnection is OleDbConnection && (paraType == DbType.DateTime))
            {
                ((OleDbParameter)parameter).OleDbType = OleDbType.Date;
            }
            else
                parameter.DbType = paraType;
            parameter.Size = size;
            return parameter;
        }

        /// <summary>
        /// 创建参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="parameterName"></param>
        /// <param name="direction"></param>
        /// <param name="paraType"></param>
        /// <param name="size"></param>
        /// <param name="Value"></param>
        /// <returns></returns>
        public DbParameter CreateCmdParameter(DbCommand cmd, string parameterName, ParameterDirection direction, DbType paraType, int size, object Value)
        {
            DbParameter parameter = this.CreateCmdParameter(cmd, parameterName, direction, paraType, size);
            parameter.Value = Value;
            return parameter;
        }

        /// <summary>
        /// 增加一个参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="paramName"></param>
        /// <param name="paramValue"></param>
        public virtual void AddCmdParamWithValue(IDbCommand cmd, string paramName, object paramValue)
        {
            if (cmd is SqlCommand)
                ((SqlCommand)cmd).Parameters.AddWithValue(paramName, paramValue);
            else if (cmd is OleDbCommand)
                ((OleDbCommand)cmd).Parameters.AddWithValue(paramName, paramValue);
            else if (cmd is System.Data.OracleClient.OracleCommand)
                ((System.Data.OracleClient.OracleCommand)cmd).Parameters.AddWithValue(paramName, paramValue);
            else
                throw new Exception("DbCommand Error!");
        }

        /// <summary>
        /// 创建 DbCommandBuilder
        /// </summary>
        /// <param name="da"></param>
        /// <returns></returns>
        public virtual DbCommandBuilder CreateCommandBuilder(DbDataAdapter da)
        {
            if (da is SqlDataAdapter)
                return new SqlCommandBuilder((SqlDataAdapter)da);
            else if (da is OleDbDataAdapter)
                return new OleDbCommandBuilder((OleDbDataAdapter)da);
            else if (da is System.Data.OracleClient.OracleDataAdapter)
                return new System.Data.OracleClient.OracleCommandBuilder((System.Data.OracleClient.OracleDataAdapter)da);
            return null;
        }
        /// <summary>
        /// 创建 DbDataAdapter
        /// </summary>
        /// <param name="selectCmd">DbCommand</param>
        /// <returns></returns>
        public virtual DbDataAdapter CreateDbAdapter(IDbCommand selectCmd)
        {
            if (selectCmd is SqlCommand)
                return new SqlDataAdapter((SqlCommand)selectCmd);
            else if (selectCmd is OleDbCommand)
                return new OleDbDataAdapter((OleDbCommand)selectCmd);
            else if (selectCmd is System.Data.OracleClient.OracleCommand)
                return new System.Data.OracleClient.OracleDataAdapter((System.Data.OracleClient.OracleCommand) selectCmd);          
            return null;
        }

        /// <summary>
        /// 创建DbDataAdapter 
        /// </summary>
        /// <param name="selectCmd"></param>
        /// <returns></returns>
        public DbDataAdapter CreateDbAdapter(string selectCmd)
        {
            IDbCommand dbCmd = this.PrepareCommand(CommandType.Text, selectCmd, null);

            return this.CreateDbAdapter(dbCmd);

        }
        /// <summary>
        /// 执行DbCommand
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(DbCommand selectCommand)
        {
            return this.ExecuteDataTable(selectCommand, null);
        }

        /// <summary>
        /// 获取一个DataTable
        /// </summary>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText)
        {
            return this.ExecuteDataTable(CommandType.Text, commandText, null);
        }

        /// <summary>
        /// 获取一个DataTable
        /// </summary>
        /// <param name="selectCommand"></param>
        /// <param name="srcTable"></param>
        /// <returns></returns>
        public virtual DataTable ExecuteDataTable(DbCommand selectCommand, string srcTable)
        {
            DataTable dataTable = new DataTable();
            using (DbDataAdapter adapter = this.CreateDbAdapter(selectCommand))
            {
                adapter.Fill(dataTable);
                dataTable.ExtendedProperties[CPropSql] = selectCommand.CommandText;
                if (!String.IsNullOrEmpty(srcTable))
                {
                    dataTable.TableName = srcTable;
                }
            }
            return dataTable;
        }

        /// <summary>
        /// 获取一个DataTable
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="srcTable"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, string srcTable)
        {
            DataTable table = this.ExecuteDataTable(CommandType.Text, commandText, null);
            if (!String.IsNullOrEmpty(srcTable))
                table.TableName = srcTable;
            return table;
        }

        /// <summary>
        /// 获取指定记录数集合的表
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="startIndex"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, int startIndex, int maxRecords)
        {
            DataTable dt;// = new DataTable();
            using (IDbCommand cmd = this.PrepareCommand(CommandType.Text, commandText, null))
            {
                dt = ExecuteDataTable(cmd, startIndex, maxRecords);
            }
            return dt;
        }

        /// <summary>
        /// 获取指定记录数集合的表
        /// </summary>
        /// <param name="selectCmd"></param>
        /// <param name="startIndex"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        public virtual DataTable ExecuteDataTable(IDbCommand selectCmd, int startIndex, int maxRecords)
        {
            DataTable dt = new DataTable();
            using (DbDataAdapter da = this.CreateDbAdapter(selectCmd))
            {
                da.Fill(startIndex, maxRecords, dt);
                dt.ExtendedProperties[CPropSql] = selectCmd.CommandText;
            }
            return dt;
        }

        /// <summary>
        /// 获取一个DataTable 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public virtual DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DataTable dataTable = new DataTable();
            using (IDbCommand selectCommand = this.PrepareCommand(cmdType, cmdText, commandParameters))
            {
                using (DbDataAdapter adapter = this.CreateDbAdapter(selectCommand))
                {
                    adapter.Fill(dataTable);
                    dataTable.ExtendedProperties[CPropSql] = cmdText;
                }
                selectCommand.Parameters.Clear();
            }
            return dataTable;
        }

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText)
        {
            return this.ExecuteNonQuery(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 执行ExecuteNonQuery
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (IDbCommand dbCmd = this.PrepareCommand(cmdType, cmdText, commandParameters))
            {
                int i = dbCmd.ExecuteNonQuery();
                dbCmd.Parameters.Clear();
                return i;
            }
        }

        /// <summary>
        /// 获取DbDataReader
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(string cmdText)
        {
            return this.ExecuteReader(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 获取DbDataReader
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            return this.ExecuteReader(cmdType, cmdText, CommandBehavior.CloseConnection, commandParameters);
        }

        /// <summary>
        /// 获取DbDataReader
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdBehavior"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, CommandBehavior cmdBehavior, params DbParameter[] commandParameters)
        {
            IDbCommand dbCmd = this.PrepareCommand(cmdType, cmdText, commandParameters);

            IDataReader read = dbCmd.ExecuteReader(cmdBehavior);
            dbCmd.Parameters.Clear();
            return read;
        }

        /// <summary>
        /// 获取ExecuteScalar
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText)
        {
            return this.ExecuteScalar(CommandType.Text, cmdText, null);
        }

        /// <summary>
        /// 获取ExecuteScalar
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="commandParameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            using (IDbCommand dbCmd = this.PrepareCommand(cmdType, cmdText, commandParameters))
            {
                object obj = dbCmd.ExecuteScalar();
                dbCmd.Parameters.Clear();
                return obj;
            }
        }

        /// <summary>
        /// 创建DbCommand
        /// </summary>
        /// <returns></returns>
        public virtual IDbCommand CreateCommand()
        {
            IDbCommand cmd = this.dbConnection.CreateCommand();
            if (this.DbTransaction != null)
                cmd.Transaction = this.DbTransaction;
            return cmd;
        }

        /// <summary>
        /// 创建DbCommand
        /// </summary>
        /// <returns></returns>
        public virtual IDbCommand CreateCommand(string commandText)
        {
            IDbCommand command = this.CreateCommand();
            command.CommandText = commandText;
            return command;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="cmdParms"></param>
        /// <returns></returns>
        protected virtual IDbCommand PrepareCommand(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)
        {
            IDbCommand command = this.CreateCommand(cmdText);
            command.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DbParameter parameter in cmdParms)
                {
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        /// <summary>
        /// 更新DataTable
        /// </summary>
        /// <param name="dataTable"></param>
        /// <returns></returns>
        public int UpdateDataTable(DataTable dataTable)
        {
            return this.UpdateDataTable(dataTable, dataTable.TableName);
        }

        /// <summary>
        /// 更新DataTable
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="srcTable"></param>
        /// <returns></returns>
        public virtual int UpdateDataTable(DataTable dataTable, string srcTable)
        {
            string cmdText = null;
            if (dataTable.ExtendedProperties.Contains(CPropSql))
            {
                cmdText = dataTable.ExtendedProperties[CPropSql].ToString();
                if (cmdText.IndexOf("select", StringComparison.OrdinalIgnoreCase) >= 0 && cmdText.IndexOf("from", StringComparison.OrdinalIgnoreCase) >= 0)
                {
                    int index = cmdText.IndexOf(" where ", StringComparison.OrdinalIgnoreCase);
                    if (index > 0)
                    {
                        cmdText = cmdText.Substring(0, index);
                    }
                    goto Flag;
                }
            }

            if (String.IsNullOrEmpty(srcTable) && String.IsNullOrEmpty(dataTable.TableName))
            {
                throw new Exception("没有设置TableName，或DataTable不是由DBConnect创建");
            }
            if (String.IsNullOrEmpty(srcTable))
                srcTable = dataTable.TableName;

            System.Text.StringBuilder builder = new System.Text.StringBuilder();
            foreach (DataColumn column in dataTable.Columns)
            {
                builder.Append(",[");
                builder.Append(column.ColumnName);
                builder.Append("]");
            }
            builder.Append(" From ");
            builder.Append(srcTable);
            cmdText = "Select " + builder.ToString(1, builder.Length - 1);

        Flag:

            using (DbDataAdapter da = this.CreateDbAdapter(this.PrepareCommand(CommandType.Text, cmdText, null)))
            {
                if (da != null)
                {
                    this.CreateCommandBuilder(da);
                    return da.Update(dataTable);
                }
            }
            return -1;
        }

        /// <summary>
        /// Return ConnectionState
        /// </summary>
        public virtual ConnectionState State
        {
            get { return this.dbConnection.State; }
        }

        /// <summary>
        /// Return or Set Connection's Trans
        /// </summary>
        public virtual IDbTransaction Transaction
        {
            get { return this.DbTransaction; }
            set { this.DbTransaction = value; }
        }

        /// <summary>
        /// Return or Set DbConnection
        /// </summary>
        public virtual IDbConnection DbConnection
        {
            get { return this.dbConnection; }
            set { this.dbConnection = value; }
        }

        /// <summary>
        /// 返回或设置连接的字符串
        /// </summary>
        public virtual string ConnectionString
        {
            get
            {
                if (dbConnection == null)
                    return string.Empty;
                return dbConnection.ConnectionString;
            }
            set
            {
                if (dbConnection != null)
                    dbConnection.ConnectionString = value;
            }
        }

    }
}
